package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.SubColumn;
import dbconn.DBConnect;
/*****
 * 子栏目管理类
 * 添加子栏目
 * 删除子栏目
 * 更新单个子栏目信息
 * 获取单个子栏目信息
 * 获取单个栏目下所有子栏目
 * @author Administrator
 *
 */
public class SubColumnControl {
	public static boolean addSubColumn(SubColumn subcolumn){
		DBConnect dbc = new DBConnect();
		String sql = "insert into subcolumn (columnid,subcolumn) value(?,?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, subcolumn.getColumnid());
			dbc.setString(2, subcolumn.getSubcolumn());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	
	//delete subcolumn
	public static boolean deleteSubColumn(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from subcolumn where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//update subcolumn
	public static boolean updateSubColumn(SubColumn subcolumn,int id){
		DBConnect dbc = new DBConnect();
		String sql = "update subcolumn set columnid=?,subcolumn=? where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, subcolumn.getColumnid());
			dbc.setString(2, subcolumn.getSubcolumn());
			dbc.setInt(3, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//get subcolumn information
	public static SubColumn getSubColumn(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from subcolumn where id=?";
		dbc.prepareStatement(sql);
		try{
			SubColumn subcolumn = new SubColumn();
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
			subcolumn.setColumnid(rs.getInt("columnid"));
			subcolumn.setSubcolumn(rs.getString("subcolumn"));
			return subcolumn;
			}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}
	
	public static int getColumn(int id){
		DBConnect dbc = new DBConnect();
		int cid = 0;
		String sql = "select columnid from subcolumn where id='"+id+"'";
		dbc.prepareStatement(sql);
		try{
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
			   cid = rs.getInt("columnid");
			}
			return cid;
		}catch(SQLException e){
			e.printStackTrace();
			return cid;
		}finally{
			dbc.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public static List<SubColumn> getSubcolumnsInOne(int columnid) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List subcolumns = new ArrayList<SubColumn>();
		String sql = "select id,columnid,subcolumn from subcolumn where columnid='"+columnid+"'";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				SubColumn subcolumn = new SubColumn();
				subcolumn.setId(rs.getInt("id"));
				subcolumn.setColumnid(rs.getInt("columnid"));
				subcolumn.setSubcolumn(rs.getString("subcolumn"));
				subcolumns.add(subcolumn);
			}
		} catch (SQLException e) {
			System.out.println("select fail！\n" + e.getMessage());
			return null;
		} finally {
			dbc.close();
		}
		return subcolumns;
	}
	
	@SuppressWarnings("unchecked")
	public static List<SubColumn> getAllSubcolumn() {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List subcolumns = new ArrayList<SubColumn>();
		String sql = "select id,columnid,subcolumn from subcolumn";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				SubColumn subcolumn = new SubColumn();
				subcolumn.setId(rs.getInt("id"));
				subcolumn.setColumnid(rs.getInt("columnid"));
				subcolumn.setSubcolumn(rs.getString("subcolumn"));
				subcolumns.add(subcolumn);
			}
		} catch (SQLException e) {
			System.out.println("select fail！\n" + e.getMessage());
			return null;
		} finally {
			dbc.close();
		}
		return subcolumns;
	}

}
